﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
using System.Data;
using System.Collections;
using System.Data.SqlClient;

namespace elr_tracking_bal
{
    public class APP_USER
    {
        public int ID_USER;
        public string USER_NAME;
        public string USER_MAIL;
        public string PASSWORD;
        public string DEFAULT_PAGE;
        public bool ACTIVACION;
        public string FLAG;
        public int ID_ROL;
        public string ROL_NAME;
        public int CHECKED;
        public string ROLES;
        public bool check;
        /// <summary>
        /// Método para insertar un usuario
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static elr_tracking_bal.UpsertStateMsg InsertUser(APP_USER user)
        { 
            APP_USER inser;
            UpsertStateMsg inserUser;
            try
            {
                int dt = elr_tracking_dal.SrvDB.ExecuteNonQuery("exec SP_APP_INSERT_USER " + "'" + user.USER_NAME +
                    "','" + user.USER_MAIL + "','" + user.PASSWORD + "','" + user.DEFAULT_PAGE + "'");

                inser = new APP_USER(user.USER_NAME, user.USER_MAIL, user.PASSWORD, user.DEFAULT_PAGE);
                inserUser = new UpsertStateMsg("Registros guardados " + dt.ToString(), true);
                return inserUser;
            }
            catch (Exception ex)    
            {
                inserUser = new UpsertStateMsg("Error al guardar " + ex, false);
                    return inserUser;
            }
        }
        /// <summary>
        /// Método para borrar un usuario
        /// </summary>
        /// <param name="ID_USER"></param>
        /// <returns></returns>
        public static elr_tracking_bal.DeleteStateMsg DeleteUser(string[] ID_USER)
        {
            DeleteStateMsg delete;
            string join;
            if (ID_USER == null)
            {
                join = "";
            }else {
                join = String.Join(",", ID_USER);
            }
            try
            {
                int dt = elr_tracking_dal.SrvDB.ExecuteNonQuery("exec SP_APP_DELETE_USER '" + join + "'");
                delete = new DeleteStateMsg("Registro eliminado" + dt.ToString(), true);
                return delete;
            }
            catch(Exception ex)
            {
                delete = new DeleteStateMsg("Error al eliminar" + ex, false);
                return delete;
            }
        }
        /// <summary>
        /// Método para modificar un usuario
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static elr_tracking_bal.UpsertStateMsg UpdateUser(APP_USER user)
        {
            APP_USER upsert;
            UpsertStateMsg upsertUser;
            try
            {
                int dt = elr_tracking_dal.SrvDB.ExecuteNonQuery("exec SP_APP_UPDATE_USER " + user.ID_USER + ",'" + user.USER_NAME +
                    "','" + user.USER_MAIL + "','" + user.PASSWORD + "','" + user.DEFAULT_PAGE + "'");

                upsert = new APP_USER(user.ID_USER, user.USER_NAME, user.USER_MAIL, user.PASSWORD, user.DEFAULT_PAGE);
                upsertUser = new UpsertStateMsg("Registros actualizados " + dt.ToString(), true);
                return upsertUser;
            }
            catch (Exception ex)
            {
                upsertUser = new UpsertStateMsg("Error al actualizar " + ex, false);
                return upsertUser;
            }
        }
        /// <summary>
        /// Método para obtener un solo usuario
        /// </summary>
        /// <param name="ID_USER"></param>
        /// <returns></returns>
        public static APP_USER GetOneUser(int ID_USER)
        {
            try
            {
                APP_USER user;
                DataTable dt = elr_tracking_dal.SrvDB.ExecuteQuery("exec SP_APP_GETONE_USER " + ID_USER + "");
                if (dt.Rows.Count == 0)
                {
                    return null;
                }
                else if (ID_USER == 0)
                {
                    return null;
                }
                else{
                    user = new APP_USER(Convert.ToInt32(dt.Rows[0]["ID_USER"]), Convert.ToString(dt.Rows[0]["USER_NAME"])
                                        ,Convert.ToString(dt.Rows[0]["USER_MAIL"]), Convert.ToString(dt.Rows[0]["PASSWORD"])
                                        , Convert.ToString(dt.Rows[0]["DEFAULT_PAGE"]));
                }
                return user;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// Obtiene la lista de roles checkeados
        /// </summary>
        /// <param name="ID_USER"></param>
        /// <returns></returns>
        public static List<APP_USER> GetListRolChecked(string ID_USER)
        {

            try
            {
                DataTable dt = elr_tracking_dal.SrvDB.ExecuteQuery("exec SP_GET_LISTROL_CHECKED '" + ID_USER + "'");
                if (dt.Rows.Count == 0)
                {
                    return null;
                }
                else
                {
                    List<APP_USER> list = dt.AsEnumerable().Select(
                        row => new APP_USER
                        {
                            ID_ROL = row.Field<int?>(0).GetValueOrDefault(),
                            ROL_NAME = String.IsNullOrEmpty(row.Field<string>(1)) ? "" : row.Field<string>(1),
                            CHECKED = row.Field<int?>(2).GetValueOrDefault(),
                        }
                        ).ToList();
                    return list;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// Método para recuperar la lista de todos los usuarios
        /// </summary>
        /// <param name="ID_USER"></param>
        /// <returns></returns>
        public static List<APP_USER> GetDataListUser(string[] ID_USER)
        {
            string join;
            if (ID_USER == null)
            {
                join = "";
            }
            else {
                join = String.Join(",", ID_USER);
            }
            try {
                DataTable dt = elr_tracking_dal.SrvDB.ExecuteQuery("exec SP_APP_GETLIST_USER");
                if (dt.Rows.Count == 0)
                {
                    return null;
                }
                else {
                    List<APP_USER> list = dt.AsEnumerable().Select(
                        row => new elr_tracking_bal.APP_USER
                        {
                            ID_USER = row.Field<int?>(0).GetValueOrDefault(),
                            USER_MAIL = String.IsNullOrEmpty(row.Field<string>(1)) ? "" : row.Field<string>(1),
                            USER_NAME = String.IsNullOrEmpty(row.Field<string>(2)) ? "" : row.Field<string>(2),
                            PASSWORD = String.IsNullOrEmpty(row.Field<string>(3)) ? "" : row.Field<string>(3),
                            DEFAULT_PAGE = String.IsNullOrEmpty(row.Field<string>(4)) ? "" : row.Field<string>(4),
                        }
                        ).ToList();
                    return list;
                }
            }
            catch(Exception ex){
                throw ex;
            }
        }
        /// <summary>
        /// Método para instertar la relación entre usuario y roles
        /// </summary>
        /// <param name="rel"></param>
        /// <returns></returns>
        public static elr_tracking_bal.UpsertStateMsg insertRelUserRol(int idUser, string roles)
        {
            UpsertStateMsg inserRel;
            try
            {
                roles = roles.Replace("'", "''");
                int dt = elr_tracking_dal.SrvDB.ExecuteNonQuery("exec SP_REL_USER_ROL " + idUser + ",'" + roles + "'");
                inserRel = new UpsertStateMsg("Registros guardados " + dt.ToString(), true);
                return inserRel;
            }
            catch (Exception ex)
            {
                inserRel = new UpsertStateMsg("Error al guardar " + ex, false);
                return inserRel;
            }
        }
        /// <summary>
        /// Método para validar inicio de sesión de usuario
        /// </summary>
        /// <param name="USER_MAIL"></param>
        /// <param name="PASSWORD"></param>
        /// <returns></returns>
        public static APP_USER ValidateUser(string USER_MAIL, string PASSWORD)
        {
            try
            {
                APP_USER user;
                DataTable dt = elr_tracking_dal.SrvDB.ExecuteQuery("exec SP_APP_VALIDATE_USER '" + USER_MAIL + "','" + PASSWORD + "'");
                if (dt.Rows.Count == 0)
                {
                    return null;
                }
                else if (USER_MAIL == "")
                {
                    return null;
                }
                else
                {
                    user = new APP_USER(Convert.ToString(dt.Rows[0]["USER_NAME"]), Convert.ToString(dt.Rows[0]["USER_MAIL"])
                                        ,Convert.ToBoolean(dt.Rows[0]["ACTIVACION"]), Convert.ToString(dt.Rows[0]["DEFAULT_PAGE"]));
                }
                return user;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// Método que cambia la columna flag a 1 para recuperar contraseña
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static string UpdateFlagRecoverUserPassword(string USER_MAIL)
        {
            //APP_USER user;
            string result;
            try
            {
                int dt = elr_tracking_dal.SrvDB.ExecuteNonQuery("exec SP_UPDATE_FLAG_RECOVER_USER_PASSWORD '" + USER_MAIL + "'");
                result = dt.ToString();
                //user = new APP_USER("Registros actualizado " + dt.ToString(), true);
                return result;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// metrodo que cambia la  columna activacion  a 1 para  activar usuario
        /// </summary>
        /// <param name="codigo"></param>
        /// <returns></returns>
        public static string UpdateflagActivacion(string codigo)
        {
            //APP_USER user;
            string result;
            try
            {
                int dt = elr_tracking_dal.SrvDB.ExecuteNonQuery("exec SP_UPDATE_FLAG_ACTIVACION '" + codigo + "'");
                result = dt.ToString();
                //user = new APP_USER("Registros actualizado " + dt.ToString(), true);
                return result;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// Método para obtener un Mail de un Usuario por medio de su código de activación
        /// </summary>
        /// <param name="codigo"></param>
        /// <returns></returns>
        public static string GetOneMail(string codigo)
        {
            try
            {
                string art;
                DataTable dt = elr_tracking_dal.SrvDB.ExecuteQuery("exec SP_GET_CORREO '" + codigo + "'");
                if (dt.Rows.Count == 0)
                {
                    return null;
                }
                else if (codigo.Length==0)
                {
                    return null;
                }
                else
                {
                    art =(Convert.ToString(dt.Rows[0]["USER_MAIL"]));
                }
                return art;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// Constructor sin parámetros
        /// </summary>
        public APP_USER() { }
        /// <summary>
        /// Constructor con un parámetro
        /// </summary>
        /// <param name="ID_USER"></param>
        public APP_USER(int ID_USER)
        {
            this.ID_USER = ID_USER;
        }
        public APP_USER(string FLAG)
        {
            this.FLAG = FLAG;
        }
        /// <summary>
        /// Constructor de 2 parámetros
        /// </summary>
        /// <param name="ID_USER"></param>
        /// <param name="ROLES"></param>
        public APP_USER(int ID_USER, string ROLES)
        {
            this.ID_USER = ID_USER;
            this.ROLES = ROLES;
        }
        /// <summary>
        /// Constructor de 3 parámetros
        /// </summary>
        /// <param name="ID_ROL"></param>
        /// <param name="ROl_NAME"></param>
        /// <param name="CHECKED"></param>
        public APP_USER(int ID_ROL, string ROL_NAME, int CHECKED)
        {
            this.ID_ROL = ID_ROL;
            this.ROL_NAME = ROL_NAME;
            this.CHECKED = CHECKED;
        }
        /// <summary>
        /// Cosnstructor de 4 parámetros 
        /// </summary>
        /// <param name="USER_NAME"></param>
        /// <param name="USER_MAIL"></param>
        /// <param name="PASSWORD"></param>
        /// <param name="DEFAULT_PAGE"></param>
        public APP_USER(string USER_NAME, string USER_MAIL, string PASSWORD, string DEFAULT_PAGE)
        {
            this.USER_NAME = USER_NAME;
            this.USER_MAIL = USER_MAIL;
            this.PASSWORD = PASSWORD;
            this.DEFAULT_PAGE = DEFAULT_PAGE;
            this.check = false;
        }
        public APP_USER(string USER_NAME, string USER_MAIL, bool ACTIVACION, string DEFAULT_PAGE)
        {
            this.USER_NAME = USER_NAME;
            this.USER_MAIL = USER_MAIL;
            this.ACTIVACION = ACTIVACION;
            this.DEFAULT_PAGE = DEFAULT_PAGE;
        }
        /// <summary>
        /// Constructor de 5 parámetros
        /// </summary>
        /// <param name="ID_USER"></param>
        /// <param name="USER_NAME"></param>
        /// <param name="USER_MAIL"></param>
        /// <param name="PASSWORD"></param>
        /// <param name="DEFAULT_PAGE"></param>
        public APP_USER(int ID_USER, string USER_NAME, string USER_MAIL, string PASSWORD, string DEFAULT_PAGE) 
        {
            this.ID_USER = ID_USER;
            this.USER_NAME = USER_NAME;
            this.USER_MAIL = USER_MAIL;
            this.PASSWORD = PASSWORD;
            this.DEFAULT_PAGE = DEFAULT_PAGE;
            this.check = false;
        }
    }
}